package com.chinawayltd.ews.sample.util.grid;

import com.chinawayltd.ews.sample.util.ReflectionsUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL工具类
 */
public class SQLUtils {

    private static final String META_PARAMETERS = "metaParameters";
    private static final String ALIAS = "temp";
    private static final String LIMIT = " limit ";
    private static final String ORDER_BY = " order by ";
    private Integer pageNo;

    /**
     * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
     *
     * @param ps              表示预编译的 SQL 语句的对象。
     * @param mappedStatement MappedStatement
     * @param boundSql        SQL
     * @param parameterObject 参数对象
     * @throws SQLException 数据库异常
     */
    public static void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null :
                    configuration.newMetaObject(parameterObject);
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {
                        value = boundSql.getAdditionalParameter(prop.getName());
                        if (value != null) {
                            value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
                        }
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }
                    @SuppressWarnings("rawtypes")
					TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                    }
                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }
    }

    /**
     * 根据数据库方言，生成特定的分页sql并处理排序语句
     * @param sql  Mapper中的Sql语句
     * @param page 分页对象
     * @return
     */
    public static String generateOrderAndPageSql(String sql, Page page) {
        String newSql = removeOrders(sql);
        StringBuffer newSqlBuf = new StringBuffer(newSql);
//        newSqlBuf.append("SELECT * FROM ( ");
//        newSqlBuf.append(newSql);
//        newSqlBuf.append(" ) " + ALIAS + " "  );
        //处理排序信息
        if(StringUtils.isNotBlank(page.getSortStr())){
            StringBuffer sortBuf = new StringBuffer(ORDER_BY);
            String columnName = page.getSortCloumn();
            if(page.getSort() != 1){//表示不要使用sortCloumn与sortType来排序，直接使用字符串拼接
            	sortBuf.append(" " +  columnName );
            }else{
            	String dir = page.getSortType();
                if(StringUtils.isNotBlank(columnName) && StringUtils.isNotBlank(dir)){
//                    sortBuf.append(" " + ALIAS + "." +  columnName + " " + dir);
                    sortBuf.append(" " + columnName + " " + dir);
                }
            }
            newSqlBuf.append(sortBuf.toString());
        }

        //如果PageSize等于-1的话，证明这个列表不需要分页。那就不用去为当前查询SQL拼接limit语句
        Integer pageSize =  page.getPageSize();
        if(pageSize != -1){
            //处理分页信息
            Integer pageNo =  page.getPageNo() - 1;
            if(pageSize != null && pageNo != null){
                newSqlBuf.append(LIMIT + (pageNo * pageSize) + "," + pageSize);
            }
        }

        return newSqlBuf.toString();
    }

    /**
     * 查询总纪录数
     * @param mappedStatement
     * @param boundSql
     * @param parameterObject
     * @return
     * @throws SQLException
     */
    public static int getSqlCount(final MappedStatement mappedStatement, final BoundSql boundSql,
                               final Object parameterObject) throws SQLException {

		final String countSql = "select count(1) from (" + removeOrders(boundSql.getSql().trim()) + ") tmp_count";
        Connection conn = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	ps = conn.prepareStatement(countSql);
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,boundSql.getParameterMappings(), parameterObject);
            SQLUtils.copyMetaParameters(boundSql, countBS);
            SQLUtils.setParameters(ps, mappedStatement, countBS, parameterObject);
            rs = ps.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }
            return count;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
            	ps.close();
            }
            if (conn != null) {
            	conn.close();
            }
        }
    }

    /**
     * 复制原执行SQL对象的metaParameters，解决MyBatis 分页foreach 参数失效
     * @param sourceBS
     * @param countBS
     */
    public static void copyMetaParameters(BoundSql sourceBS, BoundSql countBS) {
        Object metaParametersObj = ReflectionsUtils.getFieldValue(sourceBS, META_PARAMETERS);
        if (metaParametersObj != null) {
            MetaObject mo = (MetaObject) metaParametersObj;
            ReflectionsUtils.setFieldValue(countBS, META_PARAMETERS, mo);
        }
    }

    /**
     * 去除SQL order By 语句
     * @param qlString
     * @return
     */
    @SuppressWarnings("unused")
	private static String removeOrders(String qlString) {
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);  
        Matcher m = p.matcher(qlString);  
        StringBuffer sb = new StringBuffer();  
        while (m.find()) {  
            m.appendReplacement(sb, "");  
        }
        m.appendTail(sb);
        return sb.toString();  
    }
    
}
